package com.kuangjia.student_ems.repository;

import com.kuangjia.student_ems.entity.Attendance;
import com.kuangjia.student_ems.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

@Repository
public interface AttendanceRepository extends JpaRepository<Attendance, Long> {
    
    // 按日期降序查找用户的考勤记录
    @Query("SELECT a FROM Attendance a WHERE a.user = :user ORDER BY a.date DESC")
    List<Attendance> findByUser(User user);
    
    // 按日期降序查找用户在日期范围内的考勤记录
    @Query("SELECT a FROM Attendance a WHERE a.user = :user AND a.date BETWEEN :startDate AND :endDate ORDER BY a.date DESC")
    List<Attendance> findByUserAndDateBetween(User user, LocalDate startDate, LocalDate endDate);
    
    Optional<Attendance> findByUserAndDate(User user, LocalDate date);
    
    boolean existsByUserAndDate(User user, LocalDate date);
    
    // 按日期降序查找用户ID在日期范围内的考勤记录
    @Query("SELECT a FROM Attendance a WHERE a.user.id = :userId AND a.date BETWEEN :startDate AND :endDate ORDER BY a.date DESC")
    List<Attendance> findByUserIdAndDateBetween(Long userId, LocalDate startDate, LocalDate endDate);
    
    Optional<Attendance> findByUserIdAndDate(Long userId, LocalDate date);
    
    // 按日期降序查找部门在日期范围内的考勤记录
    @Query("SELECT a FROM Attendance a WHERE a.user.department.id = :departmentId AND a.date BETWEEN :startDate AND :endDate ORDER BY a.date DESC")
    List<Attendance> findByDepartmentAndDateBetween(Long departmentId, LocalDate startDate, LocalDate endDate);
    
    // 已废弃，请使用findByUserIdOrderByDateDesc
    @Deprecated
    List<Attendance> findByUserId(Long userId);
    
    @Query("SELECT COUNT(a) FROM Attendance a WHERE a.checkInTime BETWEEN :startTime AND :endTime")
    long countByClockInTimeBetween(LocalDateTime startTime, LocalDateTime endTime);
    
    @Query("SELECT COUNT(a) FROM Attendance a WHERE a.date = :date")
    long countByDate(LocalDate date);
    
    // 按日期降序查找日期范围内的考勤记录
    @Query("SELECT a FROM Attendance a WHERE a.date BETWEEN :startDate AND :endDate ORDER BY a.date DESC")
    List<Attendance> findByDateBetween(LocalDate startDate, LocalDate endDate);
    
    // 按日期降序查找指定日期之后的考勤记录
    @Query("SELECT a FROM Attendance a WHERE a.date >= :date ORDER BY a.date DESC")
    List<Attendance> findByDateGreaterThanEqual(LocalDate date);
    
    // 按状态统计考勤记录
    @Query("SELECT COUNT(a) FROM Attendance a WHERE a.status = :status")
    long countByStatus(Attendance.AttendanceStatus status);
    
    // 查找某个日期范围内特定状态的考勤记录
    @Query("SELECT COUNT(a) FROM Attendance a WHERE a.status = :status AND a.date BETWEEN :startDate AND :endDate")
    long countByStatusAndDateBetween(Attendance.AttendanceStatus status, LocalDate startDate, LocalDate endDate);
    
    // 查询所有考勤记录并按日期降序排序
    @Query("SELECT a FROM Attendance a ORDER BY a.date DESC")
    List<Attendance> findAllOrderByDateDesc();
    
    // 查找特定用户的考勤记录并按日期降序排序
    @Query("SELECT a FROM Attendance a WHERE a.user.id = :userId ORDER BY a.date DESC")
    List<Attendance> findByUserIdOrderByDateDesc(Long userId);
    
    // 模糊搜索考勤记录
    @Query("SELECT a FROM Attendance a JOIN FETCH a.user u WHERE " +
           "LOWER(u.name) LIKE LOWER(:keyword) OR " +
           "LOWER(u.username) LIKE LOWER(:keyword) OR " +
           "CAST(a.date AS string) LIKE :keyword OR " +
           "LOWER(CAST(a.status AS string)) LIKE LOWER(:keyword) OR " +
           "LOWER(a.remark) LIKE LOWER(:keyword)")
    List<Attendance> searchAttendances(@Param("keyword") String keyword);
} 